﻿




CREATE    PROCEDURE [dbo].[SP_AUTO_TRIGGER] (@TABLENAME VARCHAR(30))
AS
BEGIN
DECLARE @FIELDNAME VARCHAR(32),
	@DATATYPE VARCHAR(32),
	@FIELDLENGTH INT,
	@FIELDPREC INT,
	@FIELDSCALE INT,
	@FILEDFULLTYPE VARCHAR(50),
	@FIELDCOUNT INT,	
	@FIELDSTEP INT,
	@GETFIELDCOUNT NVARCHAR(200),
	@BASEPARAMETER VARCHAR(4000),
	@DEFAULTSQL VARCHAR(4000),
	@STOREPROCEDURE VARCHAR(8000),
	@OTHERSQL VARCHAR(4000)

SET @STOREPROCEDURE='CREATE  TRIGGER TRI_'+UPPER(@TABLENAME)+' ON dbo.'+UPPER(@TABLENAME)+''+CHAR(13)+CHAR(10)
SET @STOREPROCEDURE=@STOREPROCEDURE+'FOR INSERT,UPDATE'+CHAR(13)+CHAR(10)
SET @STOREPROCEDURE=@STOREPROCEDURE+'AS'+CHAR(13)+CHAR(10)
SET @STOREPROCEDURE=@STOREPROCEDURE+''+CHAR(13)+CHAR(10)
SET @STOREPROCEDURE=@STOREPROCEDURE+'--检测标志设置'+CHAR(13)+CHAR(10)
SET @STOREPROCEDURE=@STOREPROCEDURE+'DECLARE @CHECK INT,'+CHAR(13)+CHAR(10)
SET @STOREPROCEDURE=@STOREPROCEDURE+'--定义基表变量'+CHAR(13)+CHAR(10)

SET @GETFIELDCOUNT='SELECT @FIELDCOUNT=COUNT(*) FROM SYSOBJECTS A,SYSCOLUMNS B WHERE A.ID=B.ID AND A.NAME='''+@TABLENAME+''''
EXEC SP_EXECUTESQL @GETFIELDCOUNT,N'@FIELDCOUNT INT OUT',@FIELDCOUNT OUT

IF @FIELDCOUNT IS NULL OR @FIELDCOUNT=0 RETURN

SET @FIELDSTEP=1
SET @BASEPARAMETER='	'
SET @DEFAULTSQL='SELECT '+CHAR(13)+CHAR(10)

WHILE (@FIELDSTEP<@FIELDCOUNT)
BEGIN

   SELECT TOP 1 @DATATYPE=UPPER(C.NAME),@FIELDLENGTH=B.LENGTH,@FIELDPREC=B.prec,@FIELDSCALE=B.SCALE 
     FROM SYSOBJECTS A,SYSCOLUMNS B,SYSTYPES C 
    WHERE A.ID=B.ID AND B.XTYPE=C.XTYPE AND A.NAME=@TABLENAME AND B.COLID=@FIELDSTEP

   IF @DATATYPE IN ('INT','DATETIME','MONEY','TINYINT','BIGINT','TEXT','FLOAT','SMALLDATETIME','IMAGE')
      SET @FILEDFULLTYPE=@DATATYPE
   IF @DATATYPE IN ('CHAR','VARBINARY','VARCHAR')
      SET @FILEDFULLTYPE=@DATATYPE+'('+CAST(@FIELDLENGTH AS VARCHAR(10))+')'
   IF @DATATYPE IN ('DECIMAL','NUMERIC')
      SET @FILEDFULLTYPE=@DATATYPE+'('+CAST(@FIELDPREC AS VARCHAR(10))+','+CAST(@FIELDSCALE AS VARCHAR(10))+')'

   SELECT @FIELDNAME=COL_NAME(OBJECT_ID(@TABLENAME),@FIELDSTEP)
   SET @BASEPARAMETER=@BASEPARAMETER+'@'+@FIELDNAME+' '+@FILEDFULLTYPE+','
   SET @DEFAULTSQL=@DEFAULTSQL+'	@'+@FIELDNAME+'='+@FIELDNAME+','+CHAR(13)+CHAR(10)
   SET @FIELDSTEP=@FIELDSTEP+1
   IF @FIELDSTEP%8=0 
      SET @BASEPARAMETER=@BASEPARAMETER+CHAR(13)+CHAR(10)+'	'
END

SELECT TOP 1 @DATATYPE=C.NAME,@FIELDLENGTH=B.LENGTH,@FIELDPREC=B.prec,@FIELDSCALE=B.SCALE 
  FROM SYSOBJECTS A,SYSCOLUMNS B,SYSTYPES C WHERE A.ID=B.ID AND B.XTYPE=C.XTYPE AND A.NAME=@TABLENAME AND B.COLID=@FIELDSTEP
IF @DATATYPE IN ('INT','DATETIME','MONEY','TINYINT','BIGINT','TEXT','FLOAT','SMALLDATETIME','IMAGE')
   SET @FILEDFULLTYPE=@DATATYPE
IF @DATATYPE IN ('CHAR','VARBINARY','VARCHAR')
   SET @FILEDFULLTYPE=@DATATYPE+'('+CAST(@FIELDLENGTH AS VARCHAR(10))+')'
IF @DATATYPE IN ('DECIMAL')
   SET @FILEDFULLTYPE=@DATATYPE+'('+CAST(@FIELDPREC AS VARCHAR(10))+','+CAST(@FIELDSCALE AS VARCHAR(10))+')'

SELECT @FIELDNAME=COL_NAME(OBJECT_ID(@TABLENAME),@FIELDSTEP)
SET @BASEPARAMETER=UPPER(@BASEPARAMETER+'@'+@FIELDNAME+' '+@FILEDFULLTYPE+',')
SET @DEFAULTSQL=UPPER(@DEFAULTSQL+'	@'+@FIELDNAME+'='+@FIELDNAME+'')+CHAR(13)+CHAR(10)
SET @DEFAULTSQL=UPPER(@DEFAULTSQL+'  FROM INSERTED')+CHAR(13)+CHAR(10)

SET @BASEPARAMETER=UPPER(@BASEPARAMETER)+CHAR(13)+CHAR(10)
SET @BASEPARAMETER=UPPER(@BASEPARAMETER)+'--定义扩展变量'+CHAR(13)+CHAR(10)
SET @BASEPARAMETER=UPPER(@BASEPARAMETER)+'	@TODAY DATETIME,@TS VARCHAR(4000),@ZQLB INT'+CHAR(13)+CHAR(10)

SET @STOREPROCEDURE=@STOREPROCEDURE+@BASEPARAMETER+CHAR(13)+CHAR(10)+@DEFAULTSQL+CHAR(13)+CHAR(10)
SET @OTHERSQL='--检测标志设置'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	IF EXISTS(SELECT * FROM INSERTED WHERE GKBZ<1 OR CZY=''S_QZGK'')'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		SET @CHECK=0'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	ELSE 	'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		SET @CHECK=1   --赋值SCFBRQ  IF @SCFBRQ IS NULL AND @GKBZ=1 AND @SCBZ<>1  SET @SCFBRQ=GETDATE()   '+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'--删除记录不过触发器'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	IF @SCBZ=1 RETURN'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	SET @TODAY=CONVERT(VARCHAR(10),GETDATE(),120)'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'--模板一：此处为模板，注释信息需修改'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	IF @CHECK=1 AND (此处为模板，条件需修改)'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	BEGIN'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		RAISERROR(''此处为模板，错误信息需修改'',16,1)'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		ROLLBACK TRAN'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		RETURN'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	END'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)

SET @OTHERSQL=@OTHERSQL+'--模板二：此处为模板，注释信息需修改'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	IF @CHECK=1 AND (此处为模板，条件需修改)'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	BEGIN'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		RAISERROR(''此处为模板，错误信息需修改'',16,1)'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		ROLLBACK TRAN'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		CLOSE CUR_XXX'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		DEALLOCATE CUR_XXX'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		RETURN'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	END'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)

SET @OTHERSQL=@OTHERSQL+'--模板三：此处为模板，注释信息需修改'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	IF @CHECK=1 AND ISNULL(@TS,'''')<>'''' '+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	BEGIN'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		SET @TS='''+@TABLENAME+'错误提示：''' +'+CHAR(13)+CHAR(10)+isnull(@TS,'''') ' +CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		set @TS=REPLACE(@TS,'''+'%'+''''+','+''''')'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		RAISERROR(@TS,16,1)'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		ROLLBACK TRAN'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'		RETURN'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'	END'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)

SET @OTHERSQL=@OTHERSQL+'/************************'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'*****常用的一些表达式*****'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'1、取日月信息 CONVERT(CHAR(5),@JZRQ,10) NOT IN (''03-31'',''06-30'',''09-30'',''12-31'')'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'2、回车换行   CHAR(13)+CHAR(10)'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'*************************'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'************************/'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'/************************'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'*************************'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'*************************'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'************************/'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)

SET @OTHERSQL=@OTHERSQL+'--此处视情况进行相关字段的更新'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'UPDATE '+@TABLENAME+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'   SET FIELDNAME1=@FIELDNAME1,FIELDNAME2=@FIELDNAME2,FIELDNAME3=@FIELDNAME3,FIELDNAME4=@FIELDNAME4,'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'       FIELDNAME5=@FIELDNAME5,FIELDNAME6=@FIELDNAME6,FIELDNAME7=@FIELDNAME7,FIELDNAME8=@FIELDNAME8'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+' WHERE T_'+@TABLENAME+'_ID=@T_'+@TABLENAME+'_ID AND '+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'( ISNULL(FIELDNAME1,0)<>ISNULL(@FIELDNAME1,0) OR'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'  ISNULL(FIELDNAME2,'''')<>ISNULL(@FIELDNAME2,'''') OR'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+'  ISNULL(FIELDNAME3,''1900-01-01'')<>ISNULL(@FIELDNAME3,''1900-01-01'')'+CHAR(13)+CHAR(10)
SET @OTHERSQL=@OTHERSQL+')'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
SET @STOREPROCEDURE=@STOREPROCEDURE+@OTHERSQL
PRINT @STOREPROCEDURE 

END








